<?php
/**
 *  平時成績升級
 * @author cik
 *
 */

class Default_Model_Upgrade_ScoreNor extends Default_Model_Upgrade_Core
{
    protected $_table = "score_nor";
    protected $_targetTable = array('student_score_normal');

    public function upgrade()
    {
        if ($this->isUpgrade()) {
            return 'isUpgrde';
        }
        else {
            // 取出升級進程
            $cacheState = $this->_cacheFile."-state";
            $start = 0;
            if (is_file($cacheState)) {
                $state = unserialize(file_get_contents($cacheState));
                $start = $state['start'];
            }

            $query = "SELECT year,semester FROM score_ss GROUP BY year,semester LIMIT $start,1";

            file_put_contents($cacheState."-$start",$query);
            $res = $this->_sfs3Pdo->query($query)->fetchAll();
            $table_name = "student_score_normal_detail";

            if (count($res)==0) {
                // 更改 資料庫引擎
                $query = "ALTER TABLE `{$table_name}`  ENGINE = INNODB";
                $this->_newdbPdo->exec($query);
                // 設定 foreign key
                $query = "ALTER TABLE `$table_name`
  ADD CONSTRAINT `{$table_name}_ibfk_1` FOREIGN KEY (`normal_sn`) REFERENCES `student_score_normal` (`sn`) ON DELETE CASCADE ON UPDATE CASCADE;";
                $this->_newdbPdo->exec($query);

                $query = " update student_score_normal set sendmit='Y' WHERE subject_sn IN (SELECT subject_sn FROM student_score_stage WHERE sendmit='Y')";
                $this->_newdbPdo->exec($query);

                $log['memo'] = '平時成績';
                $log['upgrade_time']	= date("Y-m-d h:i:s");
                $this->finishUpgrade($log);
                return;
            } else {
                if ($start == 0) {
                    // 更改 資料庫引擎
                    $query = "ALTER TABLE {$table_name}  DROP FOREIGN KEY {$table_name}_ibfk_1";
                    $this->_newdbPdo->exec($query);

                    $query = "ALTER TABLE `{$table_name}`  ENGINE = MYISAM";
                    $this->_newdbPdo->exec($query);
                }
                foreach ($res as $row) {
                    // 段考成績
                    //                    $table_name = "student_score_normal_".$row['year']."_".$row['semester'];
                    $table_name_1 = "nor_score_".$row['year']."_".$row['semester'];
                    $query = "SHOW TABLES LIKE '$table_name_1'";
                    $count = $this->_sfs3Pdo->query($query);
                    if ($count->rowCount()>0) {
                        // 取出科目ss_id
                        $query2 = "SELECT  class_year, ss_id,scope_id , subject_id  FROM score_ss WHERE  year={$row['year']}
                           AND enable='1' AND semester={$row['semester']} ";
                        $ssIdRes = $this->_sfs3Pdo->query($query2)->fetchAll(PDO::FETCH_ASSOC);
                        $ssIdArr = array();
                        foreach($ssIdRes as $ssIdRow) {
                            $subjectId = $ssIdRow['subject_id'];
                            if ($subjectId == 0)
                            $subjectId = $ssIdRow['scope_id'];
                            $ssIdArr[$ssIdRow['class_year']][$subjectId] = $ssIdRow['ss_id'];
                        }

                        $query = " SELECT a.class_subj,a.stage,a.freq,a.test_name, a.weighted ,c.teacher_sn
                        FROM {$table_name_1} a  , teacher_base c WHERE a.teach_id=c.teach_id AND a.test_score >=0
                        GROUP BY a.class_subj,a.stage,a.freq  ";
                        $res2 = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
                        $temp_arr = array();

                        foreach($res2 as $row2) {
                            $arr = array();
                            $classSubjArr = explode("_",$row2['class_subj']);
                            $classYear = (int)$classSubjArr[2];
                            $classSubj =(int)$classSubjArr[4];
                            $classSn = (int)$classSubjArr[3];
                            $ssId = $ssIdArr[$classYear][$classSubj];
                            if (!$ssId) {
                                file_put_contents($cacheState."-".$row2['class_subj'],$query2.'--'.$query);
                            }
                            else {
                                $arr['subject_sn'] = $ssId;
                                $arr['test_sort'] = $row2['stage'];
                                $arr['test_name'] = $this->iconv($row2['test_name']);
                                $arr['test_order'] = $row2['freq'];
                                $arr['teacher_sn'] = $row2['teacher_sn'];
                                $arr['weighted'] = $row2['weighted'];
                                $arr['class_sn'] =  $classSn;
                                $arr['enable'] = 'Y';
                                $sn = $this->fromArray('student_score_normal',$arr);

//                                $temp_arr[$ssId][$row2['stage']][$row2['freq']] = $sn;
                            }

                        }


                        $query = "select a.* ,c.teacher_sn from $table_name_1  a   LEFT JOIN teacher_base c   ON a. teach_id=c.teach_id  WHERE a.test_score >=0";
                        $score_res = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
                        $errorArr = array();
                        foreach($score_res as $score_row) {
                            $classSubjArr = explode("_",$score_row['class_subj']);
                            $classYear = (int)$classSubjArr[2];
                            $classSubj =(int)$classSubjArr[4];
                            $classSn = (int)$classSubjArr[3];
                            $ssId = $ssIdArr[$classYear][$classSubj];
                            if (!$ssId)
                                continue;
                             // 查詢 normal_sn
                            $query = "SELECT sn FROM student_score_normal WHERE class_sn=$classSn AND subject_sn=$ssId AND
                            test_sort={$score_row['stage']} AND test_order={$score_row['freq']}  ";
                            $snTemp = $this->_newdbPdo->query($query)->fetch(PDO::FETCH_ASSOC);

                            $arr2= array();
                            $arr2['student_sn'] = $score_row['stud_sn'];
                            if ($score_row['test_score'] >=0)
                            $arr2['score'] = $score_row['test_score'];
                            $arr2['teacher_sn'] = $score_row['teacher_sn'];
                            $arr2['normal_sn'] = $snTemp['sn'];
                            if ($arr2['normal_sn']) {
                                $this->fromArray($table_name,$arr2);
                            }
                            else {
                                $errorArr[] = $arr2;
                            }

                        }
                        if (count($errorArr))
                        file_put_contents($cacheState.'-'.$table_name_1."-error" ,print_r($errorArr,1));

                    }
                }


                $state['start'] = $state['start'] +1;
                file_put_contents($cacheState,serialize($state));
                return $table_name_1." upgraded";
            }
        }
    }
}
